# -*- coding: utf-8 -*-
# @Time: 2025/2/11 0:44
# @Author: foxhuty
# @File: ai_notes.py

import pandas as pd

# 中线各科有效分
midline_scores = {
    '语文': 86,
    '数学': 49,
    '英语': 65,
    '物理': 23,
    '化学': 32,
    '生物': 37,
    '政治': 55,
    '历史': 57,
    '地理': 61
}

# 上线总分
midline_total_score = 465

# 读取文件
excel_file = pd.ExcelFile(r'D:\data_test_files\高一上期末总成绩表.xlsx')

# 获取指定工作表中的数据
df = excel_file.parse('总表')

# 统计各班各科上有效分人数
above_count = df.groupby('班级')[list(midline_scores.keys())].apply(
    lambda x: (x >= pd.Series(midline_scores)).astype(int).sum()
).reset_index()

# 统计各班上线人数
above_total_count = df.groupby('班级')[['总分']].apply(
    lambda x: (x['总分'] >= midline_total_score).astype(int).sum()
).reset_index(name='上线人数')

# 计算各班各科上有效分比例，保留两位小数
def calculate_ratio(x):
    score = midline_scores[x.name]
    return ((x >= score).astype(int).sum() / x.count()).map(lambda num: f'{num * 100:.2f}%')

above_ratio = df.groupby('班级')[list(midline_scores.keys())].apply(calculate_ratio).reset_index()

# 筛选出总分上线的学生数据
above_total_score_data = df[df['总分'] >= midline_total_score]

# 统计各班各科双有效人数
double_effective_count = above_total_score_data.groupby('班级')[list(midline_scores.keys())].apply(
    lambda x: (x >= pd.Series(midline_scores)).astype(int).sum()
).reset_index()

# 重命名双有效人数列名，添加'_双有效人数'后缀
double_effective_count = double_effective_count.rename(columns={col: f'{col}_双有效人数' for col in midline_scores.keys()})

# 合并各班各科上有效分人数和比例
result = pd.merge(above_count, above_ratio, on='班级', suffixes=('_人数', '_比例'))

# 先将上线人数合并到结果中
result = pd.merge(result, above_total_count, on='班级')

# 合并各班各科双有效人数到结果中
result = pd.merge(result, double_effective_count, on='班级')

# 计算各班各科错位生人数
for subject in midline_scores.keys():
    result[f'{subject}_错位人数'] = result['上线人数'] - result[f'{subject}_双有效人数']

# 计算各班上线比例，保留两位小数
above_total_ratio = df.groupby('班级')[['总分']].apply(
    lambda x: '{:.2f}%'.format((x['总分'] >= midline_total_score).astype(int).sum() / x['总分'].count() * 100)
).reset_index(name='上线比例')

# 合并各班上线人数和比例
result = pd.merge(result, above_total_count, on='班级')
result = pd.merge(result, above_total_ratio, on='班级')

# 统计年级合计各科上有效分人数
grade_above_count = df[list(midline_scores.keys())].apply(
    lambda x: (x >= midline_scores[x.name]).astype(int).sum()
)

# 统计年级上线人数
grade_above_total_count = (df['总分'] >= midline_total_score).astype(int).sum()

# 计算年级合计各科上有效分比例，保留两位小数
grade_above_ratio = df[list(midline_scores.keys())].apply(
    lambda x: '{:.2f}%'.format((x >= midline_scores[x.name]).astype(int).sum() / x.count() * 100)
)

# 筛选出年级总分上线的学生数据
grade_above_total_score_data = df[df['总分'] >= midline_total_score]

# 统计年级各科双有效人数
grade_double_effective_count = grade_above_total_score_data[list(midline_scores.keys())].apply(
    lambda x: (x >= midline_scores[x.name]).astype(int).sum()
)

# 计算年级各科错位生人数
grade_misaligned_count = grade_above_count - grade_double_effective_count

# 计算年级上线比例，保留两位小数
grade_above_total_ratio = '{:.2f}%'.format(grade_above_total_count / df['总分'].count() * 100)

# 创建年级汇总行
grade_summary = pd.DataFrame({
    '班级': ['年级汇总'],
    **{f'{k}_人数': [v] for k, v in grade_above_count.items()},
    **{f'{k}_比例': [v] for k, v in grade_above_ratio.items()},
    **{f'{k}_双有效人数': [v] for k, v in grade_double_effective_count.items()},
    **{f'{k}_错位人数': [v] for k, v in grade_misaligned_count.items()},
    '上线人数': [grade_above_total_count],
    '上线比例': [grade_above_total_ratio]
})

# 合并班级和年级汇总数据
final_result = pd.concat([result, grade_summary], ignore_index=True)

print(final_result)


# 将结果保存为 Excel 文件
final_result.to_excel('D:\\data_test_files\\高一上期末总成绩中线情况统计333.xlsx', index=False)
